package com.dd.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dd.entity.*;

public class DdNodeDAO extends HibernateDaoSupport {
	private static final Log log = LogFactory.getLog(DictDAO.class);

	public void saveOrUpdate(DdCode dc) {
		log.debug("saving DdCode instance");
		try {
			getHibernateTemplate().saveOrUpdate(dc);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	public DdCode findById(java.lang.Integer id) {
		log.debug("getting DdCode instance with id: " + id);
		try {
			DdCode instance = (DdCode) getHibernateTemplate().get(
					"com.dd.entity.DdCode", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
	public List findByCode(String code) {
		log.debug("findByCode :" + code);
		try {
			return this.getHibernateTemplate().find("from DdCode where code='"+code+"'");
		} catch (Exception re) {
			log.error("get failed", re);
			return new ArrayList();
		}
	}


	public List getCountPurposeData(String queryString,int type) {
		try {
			long l1 = System.currentTimeMillis();
			StringBuffer sbf = new StringBuffer();
			sbf.append("select b.node_value,count(*) as num from dd_shop a,dd_dict b "+queryString);
			sbf.append(" and b.node_code&a.purpose=b.node_code and b.node_type="+type+"  group by b.node_code order by num desc");
			Connection   conn   =   this.getSession().connection(); 
//			System.out.println(sbf.toString());
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	System.out.println("purpose use "+(System.currentTimeMillis()-l1));
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}

	}

	public List getCountPurposeData1(String queryString,int type) {
		try {
			long l1 = System.currentTimeMillis();
			StringBuffer sbf = new StringBuffer();
			sbf.append("select b.node_value,count(*) as num from dd_shopgoods a,dd_dict b "+queryString);
			sbf.append(" and b.node_code&a.purpose=b.node_code and b.node_type="+type+"  group by b.node_code order by num desc");
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
	//		System.out.println("purpose use "+(System.currentTimeMillis()-l1));
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}

	}
	public List getCountSendToData(String queryString,int type) {
		try {
			long l1 = System.currentTimeMillis();
			StringBuffer sbf = new StringBuffer();
			sbf.append("select b.node_value,count(*) as num from dd_shopgoods a,dd_dict b "+queryString);
			sbf.append(" and b.node_code&a.sendto=b.node_code and b.node_type="+type+"  group by b.node_code order by num desc");
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	System.out.println("purpose use "+(System.currentTimeMillis()-l1));
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}

	}
	public List getCountStyleData(String queryString) {
		try {
			long l1 = System.currentTimeMillis();
			StringBuffer sbf = new StringBuffer();
			
			sbf.append("select b.node_value,count(*) as num from dd_shop a,dd_dict b "+queryString);
			sbf.append(" and a.styletype=b.node_code and b.root_node='0' and b.node_type='2'  group by b.node_code order by num desc");
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	conn.close();
	//		System.out.println("style use "+(System.currentTimeMillis()-l1));
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}
	}
	public List getCountStyleData1(String queryString) {
		try {
			long l1 = System.currentTimeMillis();
			StringBuffer sbf = new StringBuffer();
			
			sbf.append("select b.node_value,count(*) as num from dd_shopgoods a,dd_dict b "+queryString);
			sbf.append(" and a.styletype=b.node_code and b.root_node='0' and b.node_type='14'  group by b.node_code order by num desc");
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	conn.close();
		//	System.out.println("style use "+(System.currentTimeMillis()-l1));
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}
	}
	public List getCountStyleChildData(String queryString, String styletype,String nodeTable) {
		try {
			StringBuffer sbf = new StringBuffer();
			sbf.append("select b.node_value,count(*) as num from dd_shop a,dd_dict b "+queryString);
			sbf.append(" and b.node_code&a.style=b.node_code and b.root_node="+styletype+"  group by b.node_code order by num desc");
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	conn.close();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}
	}
	public List getCountStyleChildData1(String queryString, String styletype,String nodeTable) {
		try {
			StringBuffer sbf = new StringBuffer();
			sbf.append("select b.node_value,count(*) as num from dd_shopgoods a,dd_dict b "+queryString);
			sbf.append(" and b.node_code&a.style=b.node_code and b.root_node="+styletype+"  group by b.node_code order by num desc");
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	conn.close();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}
	}
	public List getCountCostData(String queryString,int type) {
		try {
			long l1 = System.currentTimeMillis();
			StringBuffer sbf = new StringBuffer();
			sbf.append("select b.node_value, count(*) as num from dd_shop a,dd_dict b  "+queryString+" and a.cost=b.node_code and b.node_type="+type+" group by a.cost order by num desc");
			
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	conn.close();
		//	System.out.println("cost use "+(System.currentTimeMillis()-l1));
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}
	}
	public List getCountCostData1(String queryString,int type) {
		try {
			long l1 = System.currentTimeMillis();
			StringBuffer sbf = new StringBuffer();
			sbf.append("select b.node_value, count(*) as num from dd_shopgoods a,dd_dict b  "+queryString+" and a.cost=b.node_code and b.node_type="+type+" group by a.cost order by num desc");
			
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	conn.close();
		//	System.out.println("cost use "+(System.currentTimeMillis()-l1));
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}
	}
	public List getCountAreaData(String queryString) {
		try {
			long l1 = System.currentTimeMillis();
			StringBuffer sbf = new StringBuffer();
			sbf.append("select b.node_value, count(*) as num from dd_shop a,dd_dict b  "+queryString+" and a.area=b.node_code and b.root_node=11100 group by a.area order by num desc");
			
			Connection   conn   =   this.getSession().connection(); 
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			List list = new ArrayList();
			while(rs.next()){
				List tmpList = new ArrayList();
				tmpList.add(rs.getString("node_value"));
				tmpList.add(rs.getInt("num"));
				list.add(tmpList);
			}
			rs.close();
			stat.close();
		//	conn.close();
		//	System.out.println("Area use "+(System.currentTimeMillis()-l1));
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		}
	}
	
}
